package sma.fbss.database;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

/**
 * 
 * @author mrkhai traitement de base des données
 */
public class Database {
	private Connection conn = null;
	private String database_name;

	public Database(String db_name) throws Exception {
		this.database_name = db_name;
		this.connect();
	}

	/**
	 * Ouvrir une connexion à la base de données
	 * 
	 * @throws Exception
	 */
	private void connect() throws Exception {
		String sdriver_name = "org.sqlite.JDBC";
		Class.forName(sdriver_name);
		String spath_database_name = "jdbc:sqlite:" + "data/" + database_name;
		conn = DriverManager.getConnection(spath_database_name);
	}

	/**
	 * Récupérer les colonnes d'un tableau.
	 * 
	 * @parametre table
	 *            le nom du tableau
	 * @return un tableu de String
	 * @throws SQLException
	 */
	public String[] getColsTable(String table_name) throws SQLException {
		if (this.conn != null) {
			String str_sql = "select * from " + table_name;
			Statement stat = this.conn.createStatement();
			ResultSet rs = stat.executeQuery(str_sql);
			if (rs != null) {
				ResultSetMetaData mt = rs.getMetaData();
				int col_num = mt.getColumnCount();
				String[] fields = new String[col_num];
				for (int i = 0; i < col_num; i++)
					fields[i] = mt.getColumnLabel(i+1);
				return fields;
			} else
				return null;
		} else
			return null;

	}

	/**
	 * 
	 * @parametre sql
	 *            le sql query
	 * @return : ArrayList des données d'un tableau
	 * @throws SQLException
	 */
	public ArrayList<Map<String, String>> get(String sql)throws SQLException
	{
		ArrayList<Map<String, String>> datas = new ArrayList<Map<String, String>>();
		if(this.conn == null)
			return null;
		ResultSet rs;
		Statement stat = this.conn.createStatement();
		rs = stat.executeQuery(sql);
		if(rs == null)
			return null;
		ResultSetMetaData mt = rs.getMetaData();
		int col_num = mt.getColumnCount();
		String[] fields = new String[col_num];
		for (int i = 0; i < col_num; i++)
			fields[i] = mt.getColumnLabel(i+1);
		int field_len = fields.length;
		while (rs.next())
		{
			Map<String, String> mp = new HashMap<String, String>();
			for (int i = 0; i < field_len; i++) {
				if (rs.getString(fields[i]) != null)
					mp.put(fields[i], rs.getString(fields[i]));
			}
			datas.add(mp);
		}
		rs.close();
		return datas;
	}

	/**
	 * 
	 * @param sql
	 *            sql query
	 * @return : 0 si il y a une erreur d'excécution, au contraire, retourner 1
	 * @throws Exception
	 */
	public int set(String sql) throws Exception {
		int res = 0;
		if (this.conn != null) {
			Statement stat = this.conn.createStatement();
			res = stat.executeUpdate(sql);
			stat.close();
		}
		this.disConnect();
		this.connect();
		return res;
	}

	/**
	 * Déconnexion la base de données
	 * 
	 * @throws SQLException
	 */
	private void disConnect() throws SQLException {
		if (this.conn != null) {
			this.conn.close();
			this.conn = null;
		}
	}

	/**
	 * La déstruction
	 */
	public void finalize() {
		if (this.conn != null)
			try {
				this.disConnect();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

}
